/*
 * CargoDAO.java
 *
 * Created on 4 de diciembre de 2005, 21:56
 */
package org.fiel.admin.dao;

import java.io.PrintStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import org.fiel.FielConstants;
import org.fiel.admin.struts.forms.CargoForm;
import org.apache.log4j.Logger;

/**
 * @author Ing. Luis Pablo Gallo
 */
public class CargoDAO {
    private static Logger logger;
//    private Connection conn;

    private Connection getConnection()
    throws Exception{
        Context ctx = new InitialContext();
        DataSource ds = (DataSource)ctx.lookup(FielConstants.DATA_SOURCE_NAME);
        return ds.getConnection();
    }
    
    public CargoDAO()
    throws Exception{
//        conn = null;
        logger = Logger.getLogger(CargoDAO.class);
        
//        try{
//            Context ctx = new InitialContext();
//            DataSource ds = (DataSource)ctx.lookup(FielConstants.DATA_SOURCE_NAME);
//            conn = ds.getConnection();
//        } catch(Exception e) {
//            logger.error("Getting context", e);
//            throw e;
//        }
    }
    
    public void create(CargoForm cargo)
    throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement pstmt = null;
        try {
            conn.setAutoCommit(false);
            pstmt = conn.prepareStatement("INSERT INTO cargos (car_nombre, car_orden) VALUES(?,?)");
            pstmt.setString(1, cargo.getNombre());
            pstmt.setString(2, cargo.getOrden());
            pstmt.executeUpdate();
            conn.commit();
            conn.setAutoCommit(true);
            
            pstmt.close();
            pstmt = null;
            
            conn.close();
            conn = null;
            
        } catch(Exception e) {
            conn.rollback();
            logger.error("Executing insert sql query", e);
            throw e;
        } finally {
            try {
                if(pstmt != null)
                    pstmt.close();
                if(conn != null)
                    conn.close();
            } catch (Exception ex) {
                logger.error("Closing connection", ex);
                throw ex;
            }
        }
    }
    
    public List retrieveByArea(String areaId)
    throws Exception{
        Connection conn = this.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        List cargos = new ArrayList();
        try {
            pstmt = conn.prepareStatement("SELECT DISTINCT car_id, car_nombre, car_orden FROM cargos, recursos WHERE car_id = rec_car_id AND rec_are_id = ? ORDER BY car_orden");
            pstmt.setString(1, areaId);
            CargoForm cargo;
            
            for(rset = pstmt.executeQuery(); rset.next(); cargos.add(cargo)) {
                cargo = new CargoForm();
                cargo.setId(rset.getString("car_id"));
                cargo.setNombre(rset.getString("car_nombre"));
                cargo.setOrden(rset.getString("car_orden"));
            }
            
            rset.close();
            rset = null;
            
            pstmt.close();
            pstmt = null;
            
            conn.close();
            conn = null;
            
        } catch (Exception e) {
            logger.error("Executing selective sql query", e);
            throw e;
        } finally {
            try {
                if(rset != null)
                    rset.close();
                if(pstmt != null)
                    pstmt.close();
                if(conn != null)
                    conn.close();
            } catch (Exception ex) {
                logger.error("Closing connection", ex);
                throw ex;
            }
        }
        return cargos;
    }

    public List retrieveSinArea()
    throws Exception{
        Connection conn = this.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        List cargos = new ArrayList();
        try {
            pstmt = conn.prepareStatement("SELECT DISTINCT car_id, car_nombre, car_orden FROM cargos, recursos WHERE car_id = rec_car_id AND rec_are_id = 0 ORDER BY car_nombre");
            CargoForm cargo;
            
            for(rset = pstmt.executeQuery(); rset.next(); cargos.add(cargo)) {
                cargo = new CargoForm();
                cargo.setId(rset.getString("car_id"));
                cargo.setNombre(rset.getString("car_nombre"));
                cargo.setOrden(rset.getString("car_orden"));
            }
            
            rset.close();
            rset = null;
            
            pstmt.close();
            pstmt = null;
            
            conn.close();
            conn = null;
            
        } catch (Exception e) {
            logger.error("Executing selective sql query", e);
            throw e;
        } finally {
            try {
                if(rset != null)
                    rset.close();
                if(pstmt != null)
                    pstmt.close();
                if(conn != null)
                    conn.close();
            } catch (Exception ex) {
                logger.error("Closing connection", ex);
                throw ex;
            }
        }
        return cargos;
    }
    
    public List retrieve()
    throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        List cargos = new ArrayList();
        try {
            pstmt = conn.prepareStatement("SELECT * FROM cargos ORDER BY car_orden");
            CargoForm cargo;
            
            for(rset = pstmt.executeQuery(); rset.next(); cargos.add(cargo)) {
                cargo = new CargoForm();
                cargo.setId(rset.getString("car_id"));
                cargo.setNombre(rset.getString("car_nombre"));
                cargo.setOrden(rset.getString("car_orden"));
            }
            
            rset.close();
            rset = null;
            
            pstmt.close();
            pstmt = null;
            
            conn.close();
            conn = null;
            
        } catch (Exception e) {
            logger.error("Executing select sql query", e);
            throw e;
        } finally {
            try {
                if(rset != null)
                    rset.close();
                if(pstmt != null)
                    pstmt.close();
                if(conn != null)
                    conn.close();
            } catch(Exception ex) {
                logger.error("Closing connection", ex);
                throw ex;
            }
        }
        return cargos;
    }
    
    public CargoForm retrieve(String id)
    throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        CargoForm cargo = null;
        try {
            pstmt = conn.prepareStatement("SELECT * FROM cargos WHERE car_id = ?");
            pstmt.setString(1, id);
            rset = pstmt.executeQuery();
            if(rset.next()) {
                cargo = new CargoForm();
                cargo.setId(rset.getString("car_id"));
                cargo.setNombre(rset.getString("car_nombre"));
                cargo.setOrden(rset.getString("car_orden"));
            }
            
            rset.close();
            rset = null;
            
            pstmt.close();
            pstmt = null;
            
            conn.close();
            conn = null;
            
        } catch(Exception e) {
            logger.error("Executing select sql query", e);
            throw e;
        } finally {
            try {
                if(rset != null)
                    rset.close();
                if(pstmt != null)
                    pstmt.close();
                if(conn != null)
                    conn.close();
            } catch(Exception ex) {
                logger.error("Closing connection", ex);
                throw ex;
            }
        }
        return cargo;
    }
    
    public void update(CargoForm cargo)
    throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement pstmt = null;
        try {
            conn.setAutoCommit(false);
            pstmt = conn.prepareStatement("UPDATE cargos SET car_nombre = ?, car_orden = ? WHERE car_id = ?");
            pstmt.setString(1, cargo.getNombre());
            pstmt.setString(2, cargo.getOrden());
            pstmt.setString(3, cargo.getId());
            pstmt.executeUpdate();
            conn.commit();
            conn.setAutoCommit(true);
            
            pstmt.close();
            pstmt = null;
            
            conn.close();
            conn = null;
            
        } catch(Exception e) {
            conn.rollback();
            logger.error("Executing update sql query", e);
            throw e;
        } finally {
            try {
                if(pstmt != null)
                    pstmt.close();
                if(conn != null)
                    conn.close();
            } catch(Exception ex) {
                logger.error("Closing connection", ex);
                throw ex;
            }
        }
    }
    
    public void delete(String id)
    throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement pstmt = null;
        try {
            conn.setAutoCommit(false);
            pstmt = conn.prepareStatement("DELETE FROM cargos WHERE car_id = ?");
            pstmt.setString(1, id);
            pstmt.executeUpdate();
            conn.commit();
            conn.setAutoCommit(true);
            
            pstmt.close();
            pstmt = null;
            
            conn.close();
            conn = null;
            
        } catch(Exception e) {
            conn.rollback();
            logger.error("Executing delete sql query", e);
            throw e;
        } finally {
            try {
                if(pstmt != null)
                    pstmt.close();
                if(conn != null)
                    conn.close();
            } catch(Exception ex) {
                logger.error("Closing connection", ex);
                throw ex;
            }
        }
    }
    
}
